package com.gitee.zhziyun.meowchat.meowserver.utils;

import com.gitee.zhziyun.meowchat.meowserver.mapper.RowMapper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {
    private static final String JDBC_URL = "jdbc:sqlite:MeowServer.db"; // 默认数据库文件

    static {
        try {
            // 加载驱动（只需执行一次）
            Class.forName("org.sqlite.JDBC");
            initDatabase();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("加载SQLite JDBC驱动失败", e);
        }
    }

    public static void initDatabase() {
        try {
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            int count = stmt.executeUpdate("create table if not exists meow_users(" +
                    "id integer primary key autoincrement," +
                    "username varchar(50)," +
                    "password varchar(50)," +
                    "unique (username)" +
                    ")");
            stmt.executeUpdate("insert or ignore into meow_users(username,password) values('admin','123456')");
            stmt.executeUpdate("insert or ignore into meow_users(username,password) values('zhangsan','123456')");
        } catch (SQLException e) {
            throw new RuntimeException("初始化数据库失败", e);
        }
    }

    /**
     * 获取数据库连接
     */
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(JDBC_URL);
    }

    /**
     * 执行更新操作（增、删、改）
     * @param sql SQL语句，使用?作为占位符
     * @param params 参数数组
     * @return 受影响的行数
     */
    public static int executeUpdate(String sql, Object... params) throws Exception {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置参数
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new Exception("执行SQL失败: " + sql, e);
        }
    }

    /**
     * 执行查询操作
     * @param sql SQL语句，使用?作为占位符
     * @param params 参数数组
     * @return 结果列表，每行数据以Object数组形式存储
     */
    public static <T> List<T> executeQuery(String sql, RowMapper<T> rowMapper, Object... params) {
        List<T> result = new ArrayList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置参数
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }

            // 执行查询
            ResultSet rs = pstmt.executeQuery();

            // 映射结果集到对象列表
            while (rs.next()) {
                result.add(rowMapper.mapRow(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException("执行SQL失败: " + sql, e);
        }

        return result;
    }

    /**
     * 查询单条记录
     * @param sql SQL语句
     * @param params 参数数组
     * @return 单行记录（Object数组），如果没有结果返回null
     */
    public static <T> T querySingle(String sql, RowMapper<T> rowMapper, Object... params) {
        List<T> result = executeQuery(sql, rowMapper, params);
        return result.isEmpty() ? null : result.get(0);
    }

}
